/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.bean.UserBean;
import com.minierp.dao.PayBillDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.helper.EncCommonHelper;
import com.minierp.helper.PageHelper;
import com.minierp.model.PayBill;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class PayBillDaoImpl extends BaseDaoImpl implements PayBillDao {
	public PayBill get(String billCode) {
		String sql = "from com.minierp.model.PayBill as obj where obj.billCode = :billCode and obj.enabled=\'Y\' order by obj.id";
		String[] paramNames = new String[]{"billCode"};
		Object[] values = new Object[]{billCode};
		List list = this.getHibernateTemplate().findByNamedParam(sql,
				paramNames, values);
		return list.isEmpty() ? null : (PayBill) list.get(0);
	}

	public int delete(final long id) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.PayBill where id=:id";
						return new Integer(session.createQuery(hql)
								.setLong("id", id).executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getList(String searchKey, boolean examined, long tsId,
			UserBean ub, long estManagerId, Date allowStart, int startIndex,
			int num) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (e.estName like :estName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		String examineFlag = "N";
		String orderByStr = "order by b.payDate";
		if (examined) {
			examineFlag = "Y";
			orderByStr = "order by b.payDate desc,b.billCode desc";
		}

		String sql = "select b,e from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str
				+ " and b.examined=:examined"
				+ " and b.enabled=\'Y\' "
				+ orderByStr;
		Session sess = null;

		List arg17;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setString("examined", examineFlag);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg17 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg17;
	}

	public List getListCount(String searchKey, boolean examined, long tsId,
			UserBean ub, long estManagerId, Date allowStart) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (e.estName like :estName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		String examineFlag = "N";
		if (examined) {
			examineFlag = "Y";
		}

		String sql = "select count(b.id),sum(b.cost) from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str + " and b.examined=:examined" + " and b.enabled=\'Y\'";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setString("examined", examineFlag);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public List<?> getList(String searchKey, long tsId, UserBean ub,
			long estManagerId, Date allowStart, String orderBy, int startIndex,
			int num) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (e.estName like :estName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		String sql = "select b,e from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str + " and b.enabled=\'Y\'";
		if (orderBy != null && orderBy.equals("PAY_DATE_ASC")) {
			sql = sql + " order by b.payDate asc,b.billCode asc";
		} else {
			sql = sql + " order by b.payDate desc,b.billCode desc";
		}

		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public int getListCount(String searchKey, long tsId, UserBean ub,
			long estManagerId, Date allowStart) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((b.billCode like :billCode)  or (e.estName like :estName))";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.managerId=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		String sql = "select count(b.id) from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str + " and b.enabled=\'Y\'";
		Session sess = null;

		int arg13;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("billCode", "%" + searchKey + "%");
				q.setString("estName", "%" + searchKey + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (list.isEmpty()) {
				return 0;
			}

			arg13 = Integer.parseInt(list.get(0).toString());
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List<?> getList(String payType, long receiverId, boolean examined,
			UserBean ub) {
		String examineFlag = "N";
		if (examined) {
			examineFlag = "Y";
		}

		String sql = "from com.minierp.model.PayBill b where b.receiverId="
				+ receiverId
				+ " and b.payType=\'"
				+ payType
				+ "\'"
				+ " and b.examined=\'"
				+ examineFlag
				+ "\'"
				+ " and b.enabled=\'Y\'"
				+ EncCommonHelper
						.createTallySetSQLStr("b.tallySetId", ub, true)
				+ " order by b.payDate desc,b.billCode desc";
		return this.getHibernateTemplate().find(sql);
	}

	public BigDecimal getCost(long billId, String payType, boolean examined) {
		String examineFlag = "N";
		if (examined) {
			examineFlag = "Y";
		}

		String sql = "select sum(i.thisCost)  from com.minierp.model.PayBill b ,com.minierp.model.PayBillItem i where b.id=i.billId and i.purchaseBillId="
				+ billId
				+ " and b.payType=\'"
				+ payType
				+ "\'"
				+ " and b.examined=\'"
				+ examineFlag
				+ "\'"
				+ " and b.enabled=\'Y\'";
		List list = this.getHibernateTemplate().find(sql);
		return !list.isEmpty() && list.get(0) != null ? new BigDecimal(list
				.get(0).toString()) : new BigDecimal(0);
	}

	public Object[] getStatusValues(long id) {
		String sql = "select b.updateTime,b.enabled,b.examined from com.minierp.model.PayBill b where b.id="
				+ id;
		List list = this.getHibernateTemplate().find(sql);
		return list.size() > 0 ? (Object[]) list.get(0) : null;
	}

	public BigDecimal getSmsCost(Date startDate, Date endDate, String payType)
			throws Exception {
		String sql = "select sum(b.cost) from com.minierp.model.PayBill b where b.enabled=\'Y\' and b.examined=\'Y\' and b.payDate>=:startDate and b.payDate<:endDate and b.payType=:payType";
		Session sess = null;

		BigDecimal arg8;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			q.setString("payType", payType);
			List list = q.list();
			if (!list.isEmpty() && list.get(0) != null) {
				arg8 = new BigDecimal(list.get(0).toString());
				return arg8;
			}

			arg8 = new BigDecimal(0);
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg8;
	}

	public List<?> getPayableList(String searchKey, String estType,
			Date endDate, long tsId, UserBean ub, long estManagerId,
			Date allowStart, String cost, int startIndex, int num)
			throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((e.est_code like :est_code)  or (e.est_name like :est_name) or (e.name_spell like :name_spell))";
		}

		if (estType != null && !estType.equals("") && !estType.equals("A")) {
			str = str + " and e.est_type=:est_type";
		}

		if (endDate != null) {
			str = str + " and f.bill_date<:bill_date";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		String costStr = " rem_cost!=0";
		if (cost.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = "select a.* from(select e.id,e.est_code,e.est_name,e.est_type,sum(f.con_cost) as con_cost,sum(f.adj_cost) as adj_cost,sum(f.rp_cost) as rp_cost,sum(f.rem_cost) as rem_cost,e.bus_time,e.status,e.mark from finance_detail f,establishment e where f.est_id=e.id and f.finance_type=\'P\'"
				+ str
				+ " group by e.id"
				+ ") a"
				+ " where "
				+ costStr
				+ " order by rem_cost desc";
		Session sess = null;

		List arg18;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("est_code", "%" + searchKey + "%");
				q.setString("est_name", "%" + searchKey + "%");
				q.setString("name_spell", "%" + searchKey + "%");
			}

			if (estType != null && !estType.equals("") && !estType.equals("A")) {
				q.setString("est_type", estType);
			}

			if (endDate != null) {
				q.setDate("bill_date", endDate);
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg18 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg18;
	}

	public List getPayableListCount(String searchKey, String estType,
			Date endDate, long tsId, UserBean ub, long estManagerId,
			Date allowStart, String cost) throws Exception {
		String str = "";
		if (searchKey != null) {
			searchKey = searchKey.trim();
		}

		if (searchKey != null && !searchKey.equals("")) {
			str = " and ((e.est_code like :est_code)  or (e.est_name like :est_name) or (e.name_spell like :name_spell))";
		}

		if (estType != null && !estType.equals("") && !estType.equals("A")) {
			str = str + " and e.est_type=:est_type";
		}

		if (endDate != null) {
			str = str + " and f.bill_date<:bill_date";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		if (estManagerId != 0L) {
			str = str + " and e.manager_id=" + estManagerId;
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		String costStr = " rem_cost!=0";
		if (cost.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = "select count(id),sum(con_cost) as cCost,sum(adj_cost) as aCost,sum(rp_cost) as pCost,sum(rem_cost) as rCost from(select e.id,e.est_code,e.est_name,e.est_type,sum(f.con_cost) as con_cost,sum(f.adj_cost) as adj_cost,sum(f.rp_cost) as rp_cost,sum(f.rem_cost) as rem_cost from finance_detail f,establishment e where f.est_id=e.id and f.finance_type=\'P\'"
				+ str
				+ " group by e.id,e.est_code,e.est_name,e.est_type"
				+ ") a" + " where " + costStr;
		Session sess = null;

		List arg16;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (searchKey != null && !searchKey.equals("")) {
				q.setString("est_code", "%" + searchKey + "%");
				q.setString("est_name", "%" + searchKey + "%");
				q.setString("name_spell", "%" + searchKey + "%");
			}

			if (estType != null && !estType.equals("") && !estType.equals("A")) {
				q.setString("est_type", estType);
			}

			if (endDate != null) {
				q.setDate("bill_date", endDate);
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg16 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg16;
	}

	public List<?> getList(String payType, long billId) {
		String sql = "select b from com.minierp.model.PayBill b ,com.minierp.model.PayBillItem s where b.id=s.billId and s.purchaseBillId="
				+ billId
				+ " and b.payType=\'"
				+ payType
				+ "\'"
				+ " and b.enabled=\'Y\'" + " order by b.payDate";
		return this.getHibernateTemplate().find(sql);
	}

	public List getExamSmsCount(long[] tsIdArr) {
		if (tsIdArr != null && tsIdArr.length != 0) {
			String sql = "select b.billCode from  com.minierp.model.PayBill b where b.examSms=\'Y\'"
					+ PageHelper.createIdArrStr(tsIdArr, "b.tallySetId", true);
			return this.getHibernateTemplate().find(sql);
		} else {
			return new ArrayList();
		}
	}

	public List getExamSmsCount() {
		String sql = "select b.billCode from  com.minierp.model.PayBill b where b.examSms=\'Y\'";
		return this.getHibernateTemplate().find(sql);
	}

	public int updateExamSms() {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.PayBill set examSms=\'N\' ,updateTime=:updateTime where examSms=\'Y\'";
						Query q = session.createQuery(hql);
						q.setTimestamp("updateTime", new Date());
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int estCorporate(final long fromClientId, final long toClientId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.PayBill set receiverId="
								+ toClientId
								+ " ,updateTime=:updateTime"
								+ " where receiverId=" + fromClientId;
						Query q = session.createQuery(hql);
						q.setTimestamp("updateTime", new Date());
						return new Integer(q.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List getBaCost(long tsId, UserBean ub, Date startDate, Date endDate,
			String payType) throws Exception {
		String str = "";
		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select sum(b.cost),b.bankAccountId,b.payType from com.minierp.model.PayBill b where b.enabled=\'Y\' and b.examined=\'Y\' and b.payType=:payType and b.payDate>=:startDate and b.payDate<:endDate"
				+ str + " group by b.bankAccountId";
		Session sess = null;

		List arg11;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			q.setString("payType", payType);
			q.setDate("startDate", startDate);
			q.setDate("endDate", endDate);
			arg11 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg11;
	}

	public List getAppListCount(Date startDate, Date endDate, String estName,
			long baId, long tsId, UserBean ub, long mngId) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.payDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.payDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (baId != 0L) {
			str = str + " and b.bankAccountId=" + baId;
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost) from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str + " and b.enabled=\'Y\'";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}

	public List getAppList(Date startDate, Date endDate, String estName,
			long baId, long tsId, UserBean ub, long mngId, String orde,
			int startIndex, int num) throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.payDate>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.payDate<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.estName like :estName)";
		}

		if (baId != 0L) {
			str = str + " and b.bankAccountId=" + baId;
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String ordeStr = " order by b.payDate desc,b.billCode desc";
		if (orde != null) {
			if (orde.equals("PAY_DATE")) {
				ordeStr = " order by b.payDate desc,b.billCode desc";
			} else if (orde.equals("PAY_COST")) {
				ordeStr = " order by b.cost desc,b.billCode desc";
			} else if (orde.equals("BILL_CODE")) {
				ordeStr = " order by b.billCode desc";
			} else if (orde.equals("ESTA_NAME")) {
				ordeStr = " order by e.nameSpell desc,b.billCode desc";
			}
		}

		String sql = "select b,e.estName from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str + " and b.enabled=\'Y\'" + ordeStr;
		Session sess = null;

		List arg19;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg19 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg19;
	}

	public List getAppListForExamCount(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId) throws Exception {
		String str = "";
		if (sk != null) {
			sk = sk.trim();
		}

		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (e.estName like :sk))";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*),sum(b.cost) from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str + " and b.examined=\'N\'" + " and b.enabled=\'Y\'";
		Session sess = null;

		List arg12;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg12 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg12;
	}

	public List getAppListForExam(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId, int startIndex, int num)
			throws Exception {
		String str = "";
		if (sk != null) {
			sk = sk.trim();
		}

		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (e.estName like :sk))";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str
				+ " and b.examined=\'N\'"
				+ " and b.enabled=\'Y\'"
				+ " order by b.payDate desc,b.billCode desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public int getAppListForUnExamCount(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId) throws Exception {
		String str = "";
		if (sk != null) {
			sk = sk.trim();
		}

		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (e.estName like :sk))";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select count(*) from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str + " and b.examined=\'Y\'" + " and b.enabled=\'Y\'";
		Session sess = null;

		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			List list = q.list();
			if (!list.isEmpty() && list.get(0) != null) {
				int arg13 = Integer.parseInt(list.get(0).toString());
				return arg13;
			}
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return 0;
	}

	public List getAppListForUnExam(String sk, long tsId, UserBean ub,
			Date allowStart, long mngId, int startIndex, int num)
			throws Exception {
		String str = "";
		if (sk != null) {
			sk = sk.trim();
		}

		if (sk != null && !sk.equals("")) {
			str = " and ((b.billCode like :sk)  or (e.estName like :sk))";
		}

		if (mngId != 0L) {
			str = str + " and e.managerId=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and b.payDate>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and b.tallySetId=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tallySetId", ub,
							true);
		}

		String sql = "select b,e.estName from com.minierp.model.PayBill b,com.minierp.model.Establishment e where b.receiverId=e.id"
				+ str
				+ " and b.examined=\'Y\'"
				+ " and b.enabled=\'Y\'"
				+ " order by b.payDate desc,b.billCode desc";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			Query q = sess.createQuery(sql);
			if (sk != null && !sk.equals("")) {
				q.setString("sk", "%" + sk + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public List getAppEstUnPay(String estName, String rt, long tsId,
			UserBean ub, long mngId, Date allowStart, int startIndex, int num)
			throws Exception {
		String str = "";
		if (estName != null && !estName.equals("")) {
			str = " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		String costStr = " rem_cost!=0";
		if (rt.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = "select a.* from(select e.est_name,sum(f.rem_cost) as rem_cost,e.est_type,e.bus_time,e.mark,e.manager_name,e.area_id,e.id from finance_detail f,establishment e where f.est_id=e.id and f.finance_type=\'P\'"
				+ str
				+ " group by e.id"
				+ ") a"
				+ " where "
				+ costStr
				+ " order by rem_cost desc";
		Session sess = null;

		List arg16;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg16 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg16;
	}

	public List getAppEstUnPayTota(String estName, String rt, long tsId,
			UserBean ub, long mngId, Date allowStart) throws Exception {
		String str = "";
		if (estName != null && !estName.equals("")) {
			str = " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (allowStart != null) {
			str = str + " and f.bill_date>=:allowStart";
		}

		if (tsId != 0L) {
			str = str + " and f.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("f.tally_set_id",
							ub, true);
		}

		String costStr = " rem_cost!=0";
		if (rt.equals("ZERO")) {
			costStr = " rem_cost=0";
		}

		String sql = "select count(*),sum(rem_cost) as rem_cost from(select sum(f.rem_cost) as rem_cost from finance_detail f,establishment e where f.est_id=e.id and f.finance_type=\'P\'"
				+ str + " group by e.id" + ") a" + " where " + costStr;
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			if (allowStart != null) {
				q.setDate("allowStart", allowStart);
			}

			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	private String getAppPcListSql(String payType, Date startDate,
			Date endDate, String estName, String type, long tsId, UserBean ub,
			long mngId) {
		String payStr = "";
		String imprStr = "";
		String str = "";
		if (startDate != null) {
			if (payType.equals("COOPER")) {
				imprStr = imprStr + " and b.cooper_date>=:startDate";
			} else {
				imprStr = imprStr + " and b.purchase_date>=:startDate";
			}

			payStr = payStr + " and b.pay_date>=:startDate";
		}

		if (endDate != null) {
			if (payType.equals("COOPER")) {
				imprStr = imprStr + " and b.cooper_date<:endDate";
			} else {
				imprStr = imprStr + " and b.purchase_date<:endDate";
			}

			payStr = payStr + " and b.pay_date<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String paySql = " select b.id,e.est_name,\'PAY\' as type,b.cost,b.bank_account_id,b.pay_date as bill_date,b.creater_name,b.create_time from pay_bill b,establishment e where b.receiver_id=e.id and b.pay_type=\'"
				+ payType
				+ "\'"
				+ " and b.examined=\'Y\'"
				+ " and b.enabled=\'Y\'" + payStr + str;
		String imprSql;
		if (payType.equals("COOPER")) {
			imprSql = " select b.id,e.est_name,\'IMPR\' as type,b.imprest as cost,b.bank_account_id,b.cooper_date as bill_date,b.creater_name,b.create_time from cooper_bill b,cooper_bill_ext be,establishment e where b.id=be.id and b.cooperator_id=e.id and be.examined=\'Y\' and be.enabled=\'Y\' and b.imprest!=0"
					+ imprStr + str;
		} else {
			imprSql = " select b.id,e.est_name,\'IMPR\' as type,case when b.bill_type=\'BUY\' then b.imprest else 0-b.imprest end as cost,b.bank_account_id,b.purchase_date as bill_date,b.creater_name,b.create_time from purchase_bill b,purchase_bill_ext be,establishment e where b.id=be.id and b.provider_id=e.id and be.examined=\'Y\' and be.enabled=\'Y\' and b.imprest!=0"
					+ imprStr + str;
		}

		String sql = "";
		if (type != null && !type.equals("")) {
			if (type.equals("PAY")) {
				sql = paySql;
			} else {
				sql = imprSql;
			}
		} else {
			sql = paySql + " union all " + imprSql;
		}

		return sql;
	}

	private void setAppPcListSqlVal(Query q, Date startDate, Date endDate,
			String estName) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

		if (estName != null && !estName.equals("")) {
			q.setString("estName", "%" + estName + "%");
		}

	}

	public List getAppPcListCount(String payType, Date startDate, Date endDate,
			String estName, String type, long tsId, UserBean ub, long mngId)
			throws Exception {
		String sql = this.getAppPcListSql(payType, startDate, endDate, estName,
				type, tsId, ub, mngId);
		sql = " select count(*),sum(cost) from (" + sql + ") a";
		Session sess = null;

		List arg14;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setAppPcListSqlVal(q, startDate, endDate, estName);
			arg14 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg14;
	}

	public List getAppPcList(String payType, Date startDate, Date endDate,
			String estName, String type, long tsId, UserBean ub, long mngId,
			int startIndex, int num) throws Exception {
		String sql = this.getAppPcListSql(payType, startDate, endDate, estName,
				type, tsId, ub, mngId);
		sql = " select * from (" + sql + ") a order by bill_date desc";
		Session sess = null;

		List arg16;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setAppPcListSqlVal(q, startDate, endDate, estName);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg16 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg16;
	}
}